package com.sojson.util.db.impl;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import com.sojson.util.StringUtil;
import com.sojson.util.db.DatabaseBean;
import com.sojson.util.db.DatabaseUtilInterface;

/**
 * Database工具类实现类
 * 
 * @author liu
 * @Date 2020-06-15 14:45:59
 * @Description
 *
 */
public class DatabaseUtilImpl implements DatabaseUtilInterface {

    private static DatabaseUtilImpl databaseUtilImpl;

    private DatabaseUtilImpl() {}

    /**
     * 执行Sql插入语句
     * 
     * @param sql Sql语句
     * @param obj 参数
     * @return
     * @throws SQLException
     */
    @Override
    public int insert(String sql, Object obj[]) throws SQLException {
        Connection conn = null;
        PreparedStatement prepareStatement = null;
        int count = 0;
        try {
            conn = getConn();
            // 预编译sql,Statement.RETURN_GENERATED_KEYS返回主键
            prepareStatement = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            for (int i = 0; i < obj.length; i++) {
                // 给sql语句占位符赋值
                prepareStatement.setObject(i + 1, obj[i]);
            }
            count = prepareStatement.executeUpdate();
        } finally {
            close(conn, prepareStatement, null);
        }
        return count;
    }

    /**
     * 执行Sql插入语句(长连接)
     * 
     * @param conn
     * @param sql Sql语句
     * @param obj 参数
     * @return
     * @throws SQLException
     */
    @Override
    public int insert(DatabaseBean conn, String sql, Object obj[]) throws SQLException {
        Connection connection = null;
        PreparedStatement prepareStatement = null;
        int count = 0;
        try {
            connection = conn.getConn();
            // 预编译sql,Statement.RETURN_GENERATED_KEYS返回主键
            prepareStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            for (int i = 0; i < obj.length; i++) {
                // 给sql语句占位符赋值
                prepareStatement.setObject(i + 1, obj[i]);
            }
            count = prepareStatement.executeUpdate();
        } finally {
            close(null, prepareStatement, null);
        }
        return count;
    }

    /**
     * 执行Sql更新语句
     * 
     * @param sql Sql语句
     * @param obj 参数
     * @return
     * @throws SQLException
     */
    @Override
    public int executeUpdate(String sql, Object obj[]) throws SQLException {
        Connection conn = null;
        PreparedStatement prepareStatement = null;
        int count = 0;
        try {
            conn = getConn();
            // 预编译sql
            prepareStatement = conn.prepareStatement(sql);
            for (int i = 0; i < obj.length; i++) {
                // 给sql语句占位符赋值
                prepareStatement.setObject(i + 1, obj[i]);
            }
            count = prepareStatement.executeUpdate();
        } finally {
            close(conn, prepareStatement, null);
        }
        return count;
    }

    /**
     * 执行Sql更新语句(长连接)
     * 
     * @param conn
     * @param sql Sql语句
     * @param obj 参数
     * @return
     * @throws SQLException
     */
    @Override
    public int executeUpdate(DatabaseBean conn, String sql, Object obj[]) throws SQLException {
        Connection connection = null;
        PreparedStatement prepareStatement = null;
        int count = 0;
        try {
            connection = conn.getConn();
            // 预编译sql
            prepareStatement = connection.prepareStatement(sql);
            for (int i = 0; i < obj.length; i++) {
                // 给sql语句占位符赋值
                prepareStatement.setObject(i + 1, obj[i]);
            }
            count = prepareStatement.executeUpdate();
        } finally {
            close(null, prepareStatement, null);
        }
        return count;
    }

    /**
     * 执行Sql查询语句
     * 
     * @param sql Sql语句
     * @param obj 参数
     * @return
     * @throws SQLException
     */
    @Override
    public List<HashMap<String, Object>> executeQuery(String sql, Object obj[]) throws SQLException {
        Connection conn = null;
        PreparedStatement prepareStatement = null;
        ResultSet resu = null;
        List<HashMap<String, Object>> resultToListMap = null;
        try {
            conn = getConn();
            // 预编译sql
            prepareStatement = conn.prepareStatement(sql);
            for (int i = 0; i < obj.length; i++) {
                // 给sql语句占位符赋值
                prepareStatement.setObject(i + 1, obj[i]);
            }
            resu = prepareStatement.executeQuery();
            resultToListMap = resultToListMap(resu);
        } finally {
            close(conn, prepareStatement, resu);
        }
        return resultToListMap;
    }

    /**
     * 执行Sql查询语句(长连接)
     * 
     * @param conn
     * @param sql Sql语句
     * @param obj 参数
     * @return
     * @throws SQLException
     */
    @Override
    public List<HashMap<String, Object>> executeQuery(DatabaseBean conn, String sql, Object obj[]) throws SQLException {
        Connection connection = null;
        PreparedStatement prepareStatement = null;
        ResultSet resu = null;
        List<HashMap<String, Object>> resultToListMap = null;
        try {
            connection = conn.getConn();
            // 预编译sql
            prepareStatement = connection.prepareStatement(sql);
            for (int i = 0; i < obj.length; i++) {
                // 给sql语句占位符赋值
                prepareStatement.setObject(i + 1, obj[i]);
            }
            resu = prepareStatement.executeQuery();
            resultToListMap = resultToListMap(resu);
        } finally {
            close(null, prepareStatement, resu);
        }
        return resultToListMap;
    }

    /**
     * 获取返回结果
     * 
     * @param rs
     * @return
     * @throws SQLException
     */
    private List<HashMap<String, Object>> resultToListMap(ResultSet rs) throws SQLException {
        List<HashMap<String, Object>> list = new ArrayList<>();
        while (rs.next()) {
            ResultSetMetaData md = rs.getMetaData();
            HashMap<String, Object> map = new HashMap<>(30);
            for (int i = 1; i < md.getColumnCount(); i++) {
                map.put(md.getColumnLabel(i), rs.getObject(i));
            }
            list.add(map);
        }
        return list;
    }

    /**
     * 获取连接对象
     * 
     * @return
     * @throws SQLException
     */
    public Connection getConn() throws SQLException {
        return DriverManager.getConnection(URL, USERNAME, PASSWORD);
    }

    /**
     * 获取连接对象
     * 
     * @return
     * @throws SQLException
     */
    public Connection getConn(String url, String username, String password) throws SQLException {
        return DriverManager.getConnection(url, username, password);
    }

    /**
     * 获取连接对象
     * 
     * @return
     * @throws SQLException
     */
    public Connection getConn(String url, int port, String username, String password) throws SQLException {
        return DriverManager.getConnection(url, username, password);
    }

    /**
     * 获取连接对象
     * 
     * @return
     * @throws SQLException
     */
    @Override
    public DatabaseBean getConnection() throws SQLException {
        return new DatabaseBean(getConn());
    }

    /**
     * 获取连接对象
     * 
     * @param url 数据库路径
     * @param username 数据库帐号
     * @param password 数据库密码
     * @return
     * @throws SQLException
     */
    @Override
    public DatabaseBean getConnection(String url, String username, String password) throws SQLException {
        return new DatabaseBean(getConn(url, username, password));
    }

    /**
     * 获取连接对象
     * 
     * @param url 数据库路径
     * @param port 端口号
     * @param username 数据库帐号
     * @param password 数据库密码
     * @return
     * @throws SQLException
     */
    @Override
    public DatabaseBean getConnection(String url, int port, String username, String password) throws SQLException {
        return new DatabaseBean(getConn(url, port, username, password));
    }

    /**
     * 关闭连接(单个连接)
     * 
     * @param conn 要关闭的Connection对象
     * @throws SQLException
     */
    public void closeOne(Connection conn) throws SQLException {
        close(conn, null, null);
    }

    /**
     * 关闭连接(一组连接)
     * 
     * @param conn 要关闭的Connection对象
     * @param stat 要关闭的Statement对象
     * @param resu 要关闭的ResultSet对象
     * @throws SQLException
     */
    public void close(Connection conn, Statement stat, ResultSet resu) throws SQLException {
        if (isNotBlankObject(resu)) {
            resu.close();
        }
        if (isNotBlankObject(stat)) {
            stat.close();
        }
        if (isNotBlankObject(conn)) {
            conn.close();
        }
    }

    /**
     * 关闭连接(多组连接)
     * 
     * @param conn 要关闭的Connection对象集合
     * @param stat 要关闭的Statement对象集合
     * @param resu 要关闭的ResultSet对象集合
     * @throws SQLException
     */
    public void closeList(Connection conn, ArrayList<Statement> stat, ArrayList<ResultSet> resu) throws SQLException {
        if (isNotBlankObject(resu)) {
            for (ResultSet resultSet : resu) {
                if (isNotBlankObject(resultSet)) {
                    resultSet.close();
                }
            }
        }
        if (isNotBlankObject(stat)) {
            for (Statement statement : stat) {
                if (isNotBlankObject(statement)) {
                    statement.close();
                }
            }
        }
        if (isNotBlankObject(conn)) {
            conn.close();
        }
    }

    /**
     * 关闭连接(单个连接,顶级连接,比如:一级连接)
     * 
     * @param conn 要关闭的对象集合
     * @throws SQLException 
     */
    @Override
    public void utilsCloseOne(DatabaseBean conn) throws SQLException {
        closeOne(conn.getConn());
    }

    /**
     * 关闭连接(一组连接,比如:一级连接,二级连接,三级连接...)
     * 
     * @param conn 要关闭的对象集合
     * @throws SQLException
     */
    @Override
    public void utilsClose(DatabaseBean conn) throws SQLException {
        close(conn.getConn(), conn.getStatement(), conn.getResultSet());
    }

    /**
     * 关闭连接(多组连接,每组除顶级外都是List,顶级是一个Object,比如:顶级连接,{二级连接...},{三级连接...})
     * 
     * @param conn 要关闭的对象集合
     * @throws SQLException
     */
    @SuppressWarnings("unchecked")
    @Override
    public void utilsCloseList(DatabaseBean conn) throws SQLException {
        closeList((Connection)conn.getConnsList().get(0), (ArrayList<Statement>)conn.getConnsList().get(1),
            (ArrayList<ResultSet>)conn.getConnsList().get(2));
    }

    /**
     * 判断一个对象是否是空(空对象)
     * 
     * @param obj
     * @return
     */
    private static boolean isBlankObject(Object obj) {
        return StringUtil.isBlankObject(obj);
    }

    /**
     * 判断一个对象是否不是空(空对象)
     * 
     * @param obj
     * @return
     */
    private boolean isNotBlankObject(Object obj) {
        return StringUtil.isNotBlankObject(obj);
    }

    public static DatabaseUtilImpl getInstances() {
        if (isBlankObject(databaseUtilImpl)) {
            databaseUtilImpl = new DatabaseUtilImpl();
        }
        return databaseUtilImpl;
    }

}